from odoo import models, fields, api
from odoo import tools


class HostelRoomStudentReport(models.Model):
    _name = 'hostel.room.student.report'
    _description = 'Hostel Room Student Report'
    _auto = False
    _rec_name = 'room_name'

    # Hostel fields
    hostel_name = fields.Char(string="Hostel Name", readonly=True)
    hostel_code = fields.Char(string="Hostel Code", readonly=True)
    hostel_street = fields.Char(string="Hostel Street", readonly=True)
    
    # Room fields
    room_id = fields.Many2one('hostel.room', string="Room", readonly=True)
    room_name = fields.Char(string="Room Name", readonly=True)
    room_no = fields.Char(string="Room No.", readonly=True)
    floor_no = fields.Integer(string="Floor No.", readonly=True)
    rent_amount = fields.Monetary(string="Rent Amount", readonly=True)
    currency_id = fields.Many2one('res.currency', string="Currency", readonly=True)
    
    # Student fields
    student_id = fields.Many2one('hostel.student', string="Student", readonly=True)
    student_name = fields.Char(string="Student Name", readonly=True)
    gender = fields.Selection([
        ('male', 'Male'),
        ('female', 'Female'),
        ('other', 'Other')
    ], string="Gender", readonly=True)
    active = fields.Boolean(string="Active", readonly=True)
    check_out_date = fields.Date(string="Check-out Date", readonly=True)
    stay_duration = fields.Integer(string="Stay Duration (Days)", readonly=True)

    def init(self):
        """初始化SQL视图"""
        tools.drop_view_if_exists(self.env.cr, self._table)
        self.env.cr.execute("""
            CREATE OR REPLACE VIEW %s AS (
                SELECT 
                    ROW_NUMBER() OVER (ORDER BY h.id, r.id, s.id) AS id,
                    h.id AS hostel_id,
                    h.name AS hostel_name,
                    h.hostel_code AS hostel_code,
                    h.street AS hostel_street,
                    r.id AS room_id,
                    r.name AS room_name,
                    r.room_no AS room_no,
                    r.floor_no AS floor_no,
                    r.rent_amount AS rent_amount,
                    r.currency_id AS currency_id,
                    s.id AS student_id,
                    s.name AS student_name,
                    s.gender AS gender,
                    s.active AS active,
                    s.check_out_date AS check_out_date,
                    s.stay_duration AS stay_duration
                FROM hostel_hostel h
                LEFT JOIN hostel_room r ON r.hostel_id = h.id
                LEFT JOIN hostel_student s ON s.room_id = r.id
                WHERE h.active = true
                ORDER BY h.name, r.room_no, s.name
            )
        """ % self._table)
